package com.zhyz.bookstore.dao.impl;

import com.zhyz.bookstore.dao.ClientDao;
import com.zhyz.bookstore.entity.Book;
import com.zhyz.bookstore.entity.Category;
import com.zhyz.bookstore.entity.Favorite;
import com.zhyz.bookstore.entity.User;
import com.zhyz.bookstore.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClientDaoImpl implements ClientDao {
    Connection conn = null;
    //用户登录
    @Override
    public User login(String username, String password) {
        User user = new User();
        String sql = "select * from user where user_username = ? and user_password = ?";
        conn = JDBCUtils.getconnection();
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,username);
            pstat.setString(2,password);
            rs = pstat.executeQuery();
            if (rs.next()){
                user.setId(rs.getString("user_id"));
                user.setUsername(rs.getString("user_username"));
                user.setPassword(rs.getString("user_password"));
                user.setName(rs.getString("user_name"));
                user.setSex(rs.getString("user_sex"));
                user.setTel(rs.getString("user_tel"));
                user.setAddress(rs.getString("user_address"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(rs,pstat,conn);
        }
        return user;
    }

    //用户注册
    @Override
    public boolean register(User user) {
        String addsql = "insert into user (user_id,user_username,user_password,user_name,user_sex,user_tel,user_address) values(?,?,?,?,?,?,?)";
        conn = JDBCUtils.getconnection();
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            Statement statement = conn.createStatement();
            rs = statement.executeQuery("select * from user where user_username='" + user.getUsername() + "'");
            if (rs.next() == true){
                return true;
            }else {
                try {
                    pstat = conn.prepareStatement(addsql);
                    pstat.setString(1, user.getId());
                    pstat.setString(2, user.getUsername());
                    pstat.setString(3, user.getPassword());
                    pstat.setString(4, user.getName());
                    pstat.setString(5, user.getSex());
                    pstat.setString(6, user.getTel());
                    pstat.setString(7, user.getAddress());
                    pstat.executeUpdate();
                }catch (Exception e){
                    e.printStackTrace();
                }
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(rs,pstat,conn);
        }
    }


    public List<Book> userbook(){
        Connection connection = JDBCUtils.getconnection();
        String sql = "select * from bookdb where book_category =(select categroy_id from category where categroy_name = ?)";
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            pstat = conn.prepareStatement(sql);
            rs = pstat.executeQuery();
            List<Book> list = new ArrayList<Book>();
            while (rs.next()) {
                Book books = new Book();
                books.setBookId(rs.getString("book_id"));
                books.setBookName(rs.getString("book_name"));
                books.setBookAuthor(rs.getString("book_author"));
                books.setBookPress(rs.getString("book_press"));
                Category category = getCategory(rs.getString("book_category"));
                books.setCategory(category);
                books.setFileName(rs.getString("filename"));
                books.setPath(rs.getString("path"));
                books.setBookDesc(rs.getString("path"));
                books.setBookPrice(rs.getDouble("book_price"));
                books.setBookKunumber(rs.getInt("book_kunumber"));
                books.setBookXiaoNumber(rs.getInt("book_xiaonumber"));
                list.add(books);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public Category getCategory(String bookCategory) {
        Category category = new Category();
        Connection conn = JDBCUtils.getconnection();
        String sql = "select * from category where category_id = ?";
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,bookCategory);
            rs = pstat.executeQuery();
            if (rs.next()) {
                category.setCategoryId(rs.getString("category_id"));
                category.setCategoryName(rs.getString("category_name"));
                category.setCategoryDesc(rs.getString("category_desc"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(rs, pstat, conn);
        }

        return category;
    }

    public List<Book> getCategoryBook(String cid) {
        Connection conn = JDBCUtils.getconnection();
        String sql = "select * from bookdb";
        String sql2 = "select * from bookdb where book_category = (select category_id from category where category_id = ' '\"+cid+\"')'";
        List<Book> list = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            if (cid == null) {
                pstat = conn.prepareStatement(sql);
            } else {
                pstat = conn.prepareStatement(sql2);
            }
            rs = pstat.executeQuery();

            list = new ArrayList<Book>();
            while (rs.next()) {
                Book books = new Book();
                books.setBookId(rs.getString("book_id"));
                books.setBookName(rs.getString("book_name"));
                books.setBookAuthor(rs.getString("book_author"));
                books.setBookPress(rs.getString("book_press"));
                Category category = getCategory(rs.getString("book_category"));
                books.setCategory(category);
                books.setFileName(rs.getString("filename"));
                books.setPath(rs.getString("path"));
                books.setBookDesc(rs.getString("book_desc"));
                books.setBookKunumber(rs.getInt("book_kunumber"));
                books.setBookXiaoNumber(rs.getInt("book_xiaonumber"));
                books.setBookPrice(rs.getDouble("book_price"));
                list.add(books);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Favorite> findFavoriteByUserId(User user) {
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getconnection();
            prepareStatement = conn
                    .prepareStatement("select * from favorite where user_id = ?");
            prepareStatement.setString(1, user.getId());
            rs = prepareStatement.executeQuery();
            List<Favorite> list = new ArrayList<Favorite>();
            while (rs.next()) {
                Favorite favorite = new Favorite();
                favorite.setUser(user);
                favorite.setFavoriteId(rs.getString("favorite_id"));
                Book book = findBookById(rs.getString("book_id"));
                favorite.setBook(book);
                list.add(favorite);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, prepareStatement, conn);
        }
        return null;
    }

    @Override
    public void personInformation(User user) {
            try {
                Connection connection = JDBCUtils.getconnection();
                PreparedStatement prepareStatement = connection.prepareStatement(
                        "update user set user_name=?,user_sex=?,user_tel=?,user_address=? where user_username=?");
                prepareStatement.setString(1, user.getName());
                prepareStatement.setString(2, user.getSex());
                prepareStatement.setString(3, user.getTel());
                prepareStatement.setString(4, user.getAddress());
                prepareStatement.setString(5, user.getUsername());
                prepareStatement.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    @Override
    public void personPassword(User user) {
        try {
            Connection connection = JDBCUtils.getconnection();
            PreparedStatement prepareStatement = connection
                    .prepareStatement("update user set user_password=? where user_username=?");
            prepareStatement.setString(1, user.getPassword());
            prepareStatement.setString(2, user.getUsername());
            prepareStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Book findBookById(String book_id) {
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            Book book = new Book();
            conn = JDBCUtils.getconnection();
            pstat = conn.prepareStatement("select * from bookdb where book_id = ?");
            pstat.setString(1, book_id);
            rs = pstat.executeQuery();
            if (rs.next()) {
                book.setBookId(rs.getString("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setBookAuthor(rs.getString("book_author"));
                book.setBookPress(rs.getString("book_press"));
                Category category = getCategory(rs.getString("book_category"));
                book.setCategory(category);
                book.setFileName(rs.getString("filename"));
                book.setPath(rs.getString("path"));
                book.setBookDesc(rs.getString("book_desc"));
                book.setBookKunumber(rs.getInt("book_kunumber"));
                book.setBookKunumber(rs.getInt("book_xiaonumber"));
                book.setBookPrice(rs.getDouble("book_price"));
            }
            return book;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,pstat,conn);
        }
        return null;
    }

    @Override
    public void addfavorite(String favoriteId, String userId, String bookId) {
        PreparedStatement prepareStatement = null;
        try {
            conn = JDBCUtils.getconnection();
            prepareStatement = conn
                    .prepareStatement("insert into favorite (favorite_id,user_id,book_id) values (?,?,?)");
            prepareStatement.setString(1, favoriteId);
            prepareStatement.setString(2, userId);
            prepareStatement.setString(3, bookId);
            prepareStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, prepareStatement, conn);
        }
    }

    @Override
    public boolean findFavorite(String userId, String bookId) {
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getconnection();
            prepareStatement = conn
                    .prepareStatement("select * from favorite where user_id=? and book_id=?");
            prepareStatement.setString(1, userId);
            prepareStatement.setString(2, bookId);
            rs = prepareStatement.executeQuery();
            if (rs.next()) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, prepareStatement, conn);
        }
        throw new RuntimeException();
    }

    @Override
    public void delFavorite(String bookId) {
        PreparedStatement prepareStatement = null;
        try {
            conn = JDBCUtils.getconnection();
            prepareStatement = conn.prepareStatement("delete from favorite where book_id=?");
            prepareStatement.setString(1, bookId);
            prepareStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,prepareStatement,conn);
        }
    }

    @Override
    public List<Book> search(String search) {
        List<Book> lists = new ArrayList<Book>();
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getconnection();
            pstat = conn.prepareStatement("select * from bookdb where book_name like ?");
            pstat.setString(1, "%" + search + "%");
            rs = pstat.executeQuery();
            while (rs.next()) {
                Book books = new Book();
                books.setBookId(rs.getString("book_id"));
                books.setBookName(rs.getString("book_name"));
                books.setBookAuthor(rs.getString("book_author"));
                books.setBookPress(rs.getString("book_press"));
                Category category = getCategory(rs.getString("book_category"));
                books.setCategory(category);
                books.setFileName(rs.getString("filename"));
                books.setPath(rs.getString("path"));
                books.setBookDesc(rs.getString("book_desc"));
                books.setBookKunumber(rs.getInt("book_kunumber"));
                books.setBookKunumber(rs.getInt("book_xiaonumber"));
                books.setBookPrice(rs.getDouble("book_price"));
                lists.add(books);
            }
            return lists;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,pstat,conn);
        }
        return null;
    }



}
